from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, Session
from sqlalchemy.exc import SQLAlchemyError
import mysql.connector
from mysql.connector import Error
from .config import settings
import logging
from urllib.parse import quote_plus

# 配置日志
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

# 创建SQLAlchemy引擎
# 对密码进行URL编码以处理特殊字符
encoded_password = quote_plus(settings.DB_PASSWORD)
SQLALCHEMY_DATABASE_URL = f"mysql+mysqlconnector://{settings.DB_USER}:{encoded_password}@{settings.DB_HOST}:{settings.DB_PORT}/{settings.DB_NAME}"

# 配置连接池参数以提高性能
engine = create_engine(
    SQLALCHEMY_DATABASE_URL,
    pool_size=20,          # 连接池大小
    max_overflow=30,       # 超出pool_size后最多可创建的连接数
    pool_timeout=30,       # 获取连接的超时时间
    pool_recycle=30,   # 连接回收时间（秒）
    echo=False             # 是否输出SQL语句（生产环境建议关闭）
)

# 创建SessionLocal类
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# 创建Base类
Base = declarative_base()

def get_db_session():
    """
    获取数据库会话
    
    Returns:
        Session: SQLAlchemy数据库会话对象
    """
    db = SessionLocal()
    try:
        yield db
    except SQLAlchemyError as e:
        db.rollback()
        logger.error(f"Database error: {str(e)}")
        raise
    finally:
        db.close()

def create_connection():
    """创建数据库连接"""
    connection = None
    try:
        connection = mysql.connector.connect(
            host=settings.DB_HOST,
            port=settings.DB_PORT,
            database=settings.DB_NAME,
            user=settings.DB_USER,
            password=settings.DB_PASSWORD
        )
        if connection.is_connected():
            print("成功连接到MySQL数据库")
    except Error as e:
        print(f"连接MySQL时出错: {e}")
    return connection

def close_connection(connection):
    """关闭数据库连接"""
    if connection and connection.is_connected():
        connection.close()
        print("MySQL连接已关闭")

def init_database():
    """初始化数据库表"""
    connection = create_connection()
    if connection is None:
        return
    
    cursor = connection.cursor()
    
    # 创建用户表
    create_users_table = """
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) NOT NULL UNIQUE,
        email VARCHAR(100) NOT NULL UNIQUE,
        password_hash VARCHAR(255) NOT NULL,
        full_name VARCHAR(100),
        role_id INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (role_id) REFERENCES roles(id)
    )
    """
    
    # 创建角色表
    create_roles_table = """
    CREATE TABLE IF NOT EXISTS roles (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50) NOT NULL UNIQUE,
        description TEXT,
        permissions JSON,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )
    """
    
    # 创建患者信息表
    create_patients_table = """
    CREATE TABLE IF NOT EXISTS patients (
        id INT AUTO_INCREMENT PRIMARY KEY,
        patient_id VARCHAR(50) NOT NULL UNIQUE,
        name VARCHAR(100) NOT NULL,
        birth_date DATE,
        gender ENUM('M', 'F', 'O'),
        phone VARCHAR(20),
        address TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )
    """
    
    try:
        cursor.execute(create_users_table)
        cursor.execute(create_roles_table)
        cursor.execute(create_patients_table)
        connection.commit()
        print("数据库表初始化成功")
    except Error as e:
        print(f"创建表时出错: {e}")
        connection.rollback()
    finally:
        cursor.close()
        close_connection(connection)